MongoDB学习笔记-数据导入Excel文件

使用mongoexport工具

bin/mongoexport --host 192.168.10.10:27017 --username test --password test --db test --collection user --type=csv --fields "name,age,phone,email,address" --out data.csv

使用python的pymongo库

pip install pymongo

# -*- coding:utf-8 -*-

import sys
import xlwt
import datetime
from pymongo import MongoClient

default_encoding = 'utf-8'
if sys.getdefaultencoding() != default_encoding:
    print sys.getdefaultencoding()
    reload(sys)
    sys.setdefaultencoding(default_encoding)
    

if __name__=='__main__':  
    startTime = datetime.datetime.now()
    
    mongoClient = MongoClient("192.168.10.10", 27017)
    #连接库
    db = mongoClient.test 
    #用户认证
    db.authenticate("test","test")
    
    print db.user.count()
    
    workbook = xlwt.Workbook(encoding='utf-8')  
    
    #datas = db.user.find()
    datas = db.user.find()[0:100]

    EXCEL_ROWS = 65535
    EXCEL_COLS = 256
    nrows, total_rows, sheet_num = 0, 0, 0
    
    for data in datas:
        if (nrows % EXCEL_ROWS == 0):
            wsheet = workbook.add_sheet('sheet' + str(sheet_num), cell_overwrite_ok = True)
            nrows = 0
            sheet_num = sheet_num + 1
        keys = data.keys()
        cols_num = EXCEL_COLS if len(keys) > EXCEL_COLS else len(keys)
        for ncol in xrange(cols_num):
            value = data[keys[ncol]]
            wsheet.write(nrows, ncol, value)
        nrows = nrows + 1
        total_rows = total_rows + 1
    
    workbook.save("F:\\a.xls")
    endTime = datetime.datetime.now()
    print "import xls success ! spend time %s seconds" %((endTime - startTime).seconds)
     

# -*- coding:utf-8 -*-

import sys
import datetime
import xlsxwriter
from pymongo import MongoClient

default_encoding = 'utf-8'
if sys.getdefaultencoding() != default_encoding:
    print sys.getdefaultencoding()
    reload(sys)
    sys.setdefaultencoding(default_encoding)
    

if __name__=='__main__':  
    startTime = datetime.datetime.now()
    
    mongoClient = MongoClient("192.168.10.10", 27017)
    #连接库
    db = mongoClient.test 
    #用户认证
    db.authenticate("test","test")
    
    print db.user.count()
    
    #datas = db.user.find()
    datas = db.user.find()[0:100]

    workbook = xlsxwriter.Workbook("F:\\b.xlsx")
    
    BUFSIZE = 1024
    EXCEL_ROWS = 1040000
    EXCEL_COLS = 16384
    nrows, total_rows, sheet_num = 0, 0, 0
    
    for data in datas:
        if (nrows % EXCEL_ROWS == 0):
            worksheet = workbook.add_worksheet(name = 'sheet' + str(sheet_num))
            nrows = 0
            sheet_num = sheet_num + 1
        keys = data.keys()
        cols_num = EXCEL_COLS if len(keys) > EXCEL_COLS else len(keys)
        for ncol in xrange(cols_num):
            value = data[keys[ncol]]
            worksheet.write(nrows, ncol, value)
        nrows = nrows + 1
        total_rows = total_rows + 1
    
    workbook.close()
    endTime = datetime.datetime.now()
    print "import xlsx success ! spend time %s seconds" %((endTime - startTime).seconds)